﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;


namespace JKF.Substructure
{
    //zhaoyingnan
    public static class Excel
    {
        /// <summary>
        /// 导出excel 
        /// </summary>
        /// <param name="filename">导出Excel名字 如:操作员建档统计</param>
        /// <param name="dt">要导出的数据集 DataTable</param>
        /// <param name="columns">标题和字段的对应字典 key:字段  value:对应中文名称</param>
        /// <param name="pageType">请求类型 Excel.PageType枚举</param>
        /// <param name="response">当前Response</param>
        public static void Export(string filename, DataTable dt, IDictionary<string, string> columns, System.Web.HttpResponse response,PageType pageType)
        {
            if (dt.Rows.Count > 0)
            {
                var sb = new System.Text.StringBuilder();
                sb.Append("<?xml  version=\"1.0\"?>");
                sb.Append("<?mso-application  progid=\"Excel.Sheet\"?>");
                sb.Append("<Workbook  xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"");
                sb.Append("  xmlns:o=\"urn:schemas-microsoft-com:office:office\"");
                sb.Append("  xmlns:x=\"urn:schemas-microsoft-com:office:excel\"");
                sb.Append("  xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\"");
                sb.Append("  xmlns:html=\"http://www.w3.org/TR/REC-html40\">");
                sb.Append("  <DocumentProperties  xmlns=\"urn:schemas-microsoft-com:office:office\">");
                sb.Append("    <Author></Author>");
                sb.Append(string.Format("    <Created>{0}T{1}Z</Created>", DateTime.Now.ToString("yyyy-mm-dd"), DateTime.Now.ToString("HH:MM:SS")));
                sb.Append("    <Company></Company>");
                sb.Append("    <Version>12.00</Version>");
                sb.Append("  </DocumentProperties>");
                sb.Append("  <ExcelWorkbook  xmlns=\"urn:schemas-microsoft-com:office:excel\">");
                sb.Append("    <WindowHeight>8955</WindowHeight>");
                sb.Append("    <WindowWidth>11355</WindowWidth>");
                sb.Append("    <WindowTopX>480</WindowTopX>");
                sb.Append("    <WindowTopY>15</WindowTopY>");
                sb.Append("    <ProtectStructure>False</ProtectStructure>");
                sb.Append("    <ProtectWindows>False</ProtectWindows>");
                sb.Append("  </ExcelWorkbook>");
                sb.Append("  <Styles>");
                sb.Append("    <Style  ss:ID=\"Default\"  ss:Name=\"Normal\">");
                sb.Append("      <Alignment  ss:Vertical=\"Bottom\"/>");
                sb.Append("      <Borders/>");
                sb.Append("      <Font x:Family=\"Swiss\"/>");
                sb.Append("      <Interior/>");
                sb.Append("      <Protection/>");
                sb.Append("    </Style>");
                sb.Append("    <Style  ss:ID=\"s21\">");
                sb.Append("      <Alignment  ss:Vertical=\"Bottom\"  ss:WrapText=\"1\"/>");
                sb.Append("    </Style>");
                sb.Append("    <Style  ss:ID=\"s22\">");
                sb.Append("      <Font ss:FontName=\"宋体\" x:CharSet=\"134\"/>");
                sb.Append("    </Style>");
                sb.Append("    <Style  ss:ID=\"s23\">");
                sb.Append("    <Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Center\"/>");
                sb.Append("      <Font ss:FontName=\"宋体\" x:CharSet=\"134\" ss:Bold=\"1\"/>");
                sb.Append("    </Style>");
                sb.Append("    <Style  ss:ID=\"s26\">");
                sb.Append("      <Font ss:FontName=\"宋体\" x:CharSet=\"134\"/>");
                sb.Append("      <NumberFormat ss:Format=\"@\"/>");
                sb.Append("    </Style>");
                sb.Append("  </Styles>");


                sb.Append("  <Worksheet  ss:Name=\"Sheet1\">");
                sb.Append("    <Table ss:DefaultColumnWidth=\"100\">");
                for (int i = 0; i < columns.Keys.Count; i++)
                {
                    sb.Append("<Column ss:Index=\"" + (i + 1) + "\" ss:AutoFitWidth=\"0\" ss:Width=\"100\"/>");
                }

                sb.Append("<Row>");

                //添加列

                foreach (string item in columns.Values)
                {
                    sb.Append("<Cell ss:StyleID=\"s23\"><Data  ss:Type=\"String\">" + item + "</Data></Cell>");
                }

                sb.Append("</Row>");

                //添加数据行


                foreach (DataRow dr in dt.Rows)
                {
                    sb.Append("<Row>");

                    foreach (var item in columns.Keys)
                    {
                        sb.Append("<Cell ss:StyleID=\"s26\"><Data  ss:Type=\"String\">" + dr[item] + "</Data></Cell>");
                    }

                    sb.Append("</Row>");
                }

                sb.Append("    </Table>");
                sb.Append("    <WorksheetOptions  xmlns=\"urn:schemas-microsoft-com:office:excel\">");
                sb.Append("      <ProtectObjects>False</ProtectObjects>");
                sb.Append("      <ProtectScenarios>False</ProtectScenarios>");
                sb.Append("    </WorksheetOptions>");
                sb.Append("  </Worksheet>");

                sb.Append("</Workbook>");

                response.Buffer = true;
                response.ClearContent();
                response.AddHeader("content-disposition", "attachment; filename=" + System.Web.HttpUtility.UrlEncode(filename, System.Text.Encoding.UTF8) + DateTime.Now.ToString("-yyyyMMdd") + ".xls");
                response.ContentType = "application/excel";
                response.Write(sb.ToString());
                if (pageType == 0)
                {
                    response.End();
                }
            }
            else
            {
                response.Write("<script>alert('没有要导出的数据');parent.window.close();</script>");
            }
        }

        public enum PageType
        {
            aspx=0,
            handler=1
        }
        
    }

    
}
